package com.seed.core.dialect.impl;

import cn.hutool.core.util.StrUtil;
import com.mybatisflex.core.FlexGlobalConfig;
import com.mybatisflex.core.datasource.FlexDataSource;
import com.mybatisflex.core.row.Db;
import com.mybatisflex.core.row.Row;
import com.seed.core.column.Column;
import com.seed.core.dialect.DialectDDL;
import com.seed.core.enumeration.ColumnType;
import com.seed.core.pojo.Const;
import com.seed.core.pojo.seed.Seed;
import org.apache.ibatis.jdbc.ScriptRunner;

import java.io.BufferedReader;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.Statement;
import java.util.*;

public class MysqlDialectDDL implements DialectDDL {

    public void run(Map<String, Seed> seeds) {
        if(seeds == null || seeds.isEmpty()) return;

        List<Object> existTableNames = Db.selectObjectList("show tables");
        FlexDataSource flexDataSource = FlexGlobalConfig.getDefaultConfig().getDataSource();
        try {
            Connection connection = flexDataSource.getConnection();
//            connection.setAutoCommit(false); // TODO 好像不生效
            executeCreateDDL(connection, seeds);
            executeAddColumnsDDL(connection, seeds);
        } catch (Exception e) {
            throw new RuntimeException("执行脚本出错", e);
        }
    }

    private void executeCreateDDL(Connection connection, Map<String, Seed> seeds) throws Exception {
        List<Object> existTableNames = Db.selectObjectList("show tables");
        Statement statement = connection.createStatement();
        for(Map.Entry<String, Seed> entry: seeds.entrySet()) {
            Seed seed = entry.getValue();
            String tableName = getTableName(seed);
            String ddl = createTableDDl(seed);
//            System.out.println("create ddl=============");
//            System.out.println(ddl);
            if(!existTableNames.contains(tableName)) statement.addBatch(ddl);
        }

        statement.executeBatch();
    }

    private void executeAddColumnsDDL(Connection connection, Map<String, Seed> seeds) throws Exception {
        Statement statement = connection.createStatement();
//        System.out.println("addColumnDDL====================");
        for(Map.Entry<String, Seed> entry: seeds.entrySet()) {
            Seed seed = entry.getValue();
            for(String ddl: addColumnsDDL(seed)) {
//                System.out.println(ddl);
                statement.addBatch(ddl);
            }
        }

        statement.executeBatch();
    }

    public String getCreateScript(Map<String, Seed> seeds, List<Object> existTableNames) {
        StringBuilder script = new StringBuilder();
        for(Map.Entry<String, Seed> entry: seeds.entrySet()) {
            Seed seed = entry.getValue();
            String tableName = getTableName(seed);
            if(!existTableNames.contains(tableName)) script.append(createTableDDl(seed));
        }

        return script.toString();
    }

    private void alterTable(Seed seed) {
        String tableName = getTableName(seed);
        List<Row> oldColumns = Db.selectListBySql(String.format("DESCRIBE `%s`", tableName));
        List<String> oldColumnNames = oldColumns.stream().map(row -> row.getString("Field")).toList();
        Set<String> newColumnNames = seed.getColumns().keySet();
        String addTpl = "ALTER TABLE `%s` ADD COLUMN `%s` %s %s %s %s%s;";
        String pre = null;
        for(String columnName: newColumnNames) {
            if(oldColumnNames.contains(columnName)) {
                pre = columnName;
            } else {
                Column column = seed.getColumns().get(columnName);
                String type = type(column);
                String defVal = defVal(column);
                String required = required(column);
                String comment = comment(column);
                String position = pre == null ? " FIRST" : String.format(" AFTER `%s`", pre);
                run(String.format(addTpl, tableName, columnName, type, defVal, required, comment, position));
            }
        }

        String removeTpl = "ALTER TABLE `%s` DROP COLUMN `%s`;";
        for(String columnName: oldColumnNames) {
            if(!newColumnNames.contains(columnName)) {
                run(String.format(removeTpl, tableName, columnName));
            }
        }

        String modifyTpl = "ALTER TABLE `%s` MODIFY COLUMN `%s` %s %s %s%s;";
        for(Row oldColumn: oldColumns) {
            String columnName = oldColumn.getString("Field");
            if(!newColumnNames.contains(columnName)) continue;

            String oldType = oldColumn.getString("Type");
            String oldNull = oldColumn.getString("Null");
            String oldDefault = oldColumn.getString("Default");
            Column column = seed.getColumns().get(columnName);
            String type = type(column);
            String required = required(column);
            String nil = required.equals("NULL") ? "YES" : "NO";
            String defVal = column.getDefVal();
            if(!StrUtil.equalsIgnoreCase(oldType, type) || !StrUtil.equals(oldNull, nil) || !StrUtil.equals(oldDefault, defVal)) {
                run(String.format(modifyTpl, tableName, columnName, type, required, defVal(column), comment(column)));
            }
        }
    }

    private void updateHabtm(Seed seed) {
//        LinkedHashMap<String, Habtms> habtmMap = seed.getHabtm();
//        if(habtmMap == null) return;
//
//        for (HabtmWrap habtm: habtmMap.values()) {
//            String alias = habtm.getAlias();
//            Seed targetSeed = seeds.get(alias);
//            if(targetSeed == null) {
//                throw new RuntimeException(String.format("【%s】下关联的【%s】不存在", seed.getName(), alias));
//            }
//
//            String through = habtm.through();
//            String throughSelfColumn = habtm.throughSelfColumn();
//            String throughTargetColumn = habtm.throughTargetColumn();
//            if(existTableNames.contains(through)) {
//                // TODO
//            } else {
//                ColumnWrap selfColumn = seed.getColumns().get(throughSelfColumn);
//                ColumnWrap targetColumn = targetSeed.getColumns().get(throughTargetColumn);
//                String template = """
//                    CREATE TABLE IF NOT EXISTS `%s` (
//                      %s
//                      PRIMARY KEY (`id`) USING BTREE
//                    ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC%s;
//                    """;
//
//                String columnTemplate = "`%s` %s %s %s%s,\r\n  ";
//                StringBuilder sb = new StringBuilder("`id` char(20) NOT NULL COMMENT 'ID',\r\n  ");
//                String selfType = type(selfColumn);
//                String selfDefVal = defVal(selfColumn);
//                String selfRequired = required(selfColumn);
//                String selfComment = comment(selfColumn);
//                sb.append(String.format(columnTemplate, throughSelfColumn, selfType, selfDefVal, selfRequired, selfComment));
//
//                String targetType = type(targetColumn);
//                String targetDefVal = defVal(targetColumn);
//                String targetRequired = required(targetColumn);
//                String targetComment = comment(targetColumn);
//                sb.append(String.format(columnTemplate, throughTargetColumn, targetType, targetDefVal, targetRequired, targetComment));
//
//                String comment = comment(habtm);
//                run(String.format(template, through, sb, comment));
//            }
//        }
    }

    private void run(String script) {
        try {
            Connection connection = FlexGlobalConfig.getDefaultConfig().getDataSource().getConnection();
            ScriptRunner sr = new ScriptRunner(connection);
            Reader reader = new BufferedReader(new StringReader(script));
            sr.runScript(reader);
        } catch (Exception e) {
            throw new RuntimeException("执行脚本出错", e);
        }
    }

    private String createTableDDl(Seed seed) {
        String template = """
            CREATE TABLE IF NOT EXISTS `%s` (
              %s
              PRIMARY KEY (`id`) USING BTREE
            ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC%s;
            """;
        String tableName = getTableName(seed);
        String columns = columns(seed);
        String comment = comment(seed);
        return String.format(template, tableName, columns, comment);
    }

    private List<String> addColumnsDDL(Seed seed) {
        List<String> ret = new ArrayList<>();
        String tableName = getTableName(seed);
        List<Row> oldColumns = Db.selectListBySql(String.format("DESCRIBE `%s`", tableName));
        List<String> oldColumnNames = oldColumns.stream().map(row -> row.getString("Field")).toList();
        Set<String> newColumnNames = seed.getColumns().keySet();
        String addColumnTpl = "ALTER TABLE `%s` ADD COLUMN `%s` %s %s %s %s%s;";
        String pre = null;
        for(String columnName: newColumnNames) {
            if(oldColumnNames.contains(columnName)) {
                pre = columnName;
            } else {
                Column column = seed.getColumns().get(columnName);
                String type = type(column);
                String defVal = defVal(column);
                String required = required(column);
                String comment = comment(column);
                String position = pre == null ? " FIRST" : String.format(" AFTER `%s`", pre);
                ret.add(String.format(addColumnTpl, tableName, columnName, type, defVal, required, comment, position));
            }
        }

        return ret;
    }

    private String getTableName(Seed seed) {
        return seed.getTableName();
    }

    private String columns(Seed seed) {
        String template = "`%s` %s %s %s%s,\r\n  ";
        StringBuilder ret = new StringBuilder();
        Map<String, Column> columns = seed.getColumns();
        for(String columnName: columns.keySet()) {
            Column column = columns.get(columnName);
            String type = type(column);
            String defVal = defVal(column);
            String required = required(column);
            String comment = comment(column);
            ret.append(String.format(template, column.getName(), type, defVal, required, comment));
        }

        return ret.toString();
    }

    private String type(Column column) {
        ColumnType t = column.getType();
        Integer length = column.getLength();
        Integer scale = column.getScale();
        Integer precision = column.getPrecision();
        Map<String, String> options = column.getOptions();
        return switch (t) {
            case VARCHAR, PASSWORD, ICON, QRCODE -> String.format("%s(%s)", t, length == null ? 64 : length);
            case CHAR -> String.format("%s(%s)", t, length == null ? 17 : length);
            case TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT -> length == null ? t.getValue() : String.format("%s(%s)", t, length);
            case FLOAT, DOUBLE, DECIMAL -> String.format("%s(%s, %s)", t, scale == null ? 10 : scale, precision == null ? 2 : precision);
            case BIT -> String.format("%s", t);
            case ENUM, SET -> String.format("%s(%s)", t, toStrList(options));
            case FILE, IMAGE, AVATAR -> String.format("VARCHAR(%s)", length == null ? 255 : length);
            case COLOR -> String.format("VARCHAR(%s)", length == null ? 8 : length);
            case REF -> "CHAR(17)";
            case YESNO -> "ENUM('yes', 'no')";
            case SEX -> "ENUM('male', 'female')";
            case AGE -> "TINYINT UNSIGNED";
            default -> t.getValue();
        };
    }

    private String defVal(Column column) {
        String defVal = column.getDefVal();
        return StrUtil.isBlank(defVal) ? "" : String.format("DEFAULT %s", defVal);
    }

    private String required(Column column) {
        Boolean required = column.getRequired();
        required = (required != null && required) || Const.ID.equals(column.getName());
        return required ? "NOT NULL" : "NULL";
    }

    private String comment(Column column) {
        String label = column.getLabel();
        String comment = column.getComment();
        if(StrUtil.isBlank(comment)) comment = StrUtil.isBlank(label) ? "" : label;

        return StrUtil.isBlank(comment) ? "" : String.format(" COMMENT '%s'", comment);
    }

    private String comment(Seed seed) {
        String label = seed.getLabel();
        String comment = seed.getComment();
        if(StrUtil.isBlank(comment)) comment = StrUtil.isBlank(label) ? "" : label;

        return StrUtil.isBlank(comment) ? "" : String.format(" COMMENT '%s'", comment);
    }

    private String toStrList(Map<String, String> options) {
        Set<String> set = options == null ? Collections.emptySet() : options.keySet();
        StringBuilder sb = new StringBuilder();
        for(String item: set) {
            if(!sb.isEmpty()) sb.append(",");
            sb.append(String.format("'%s'", item));
        }

        return sb.toString();
    }

//    private String comment(Habtms habtm) {
//        String label = habtm.getLabel();
//        String comment = habtm.getComment();
//        if(StrUtil.isBlank(comment)) comment = StrUtil.isBlank(label) ? "" : label;
//
//        return StrUtil.isBlank(comment) ? "" : String.format(" COMMENT '%s'", comment);
//    }
}
